﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace InformationSystem
{
	/// <summary>
	/// Description of SQLiteHelper.
	/// </summary>
	/// 
	public class SQLiteHelper
	{
		public static String _connectionString = "Data Source=$PATH$\\IS.db3;Version=3;";
        //public static String _connectionString = @"Data Source=F:\git\20161209\code\InformationSystem\InformationSystem\bin\Debug\IS.db3;Version=3;";
			
		SQLiteConnection _connection;
		DbTransaction trans;
		public SQLiteHelper()	
		{

            _connection = new SQLiteConnection(_connectionString.Replace("$PATH$\\", ""));
		
		}
		
		public SQLiteHelper(string connectionString)
		
			:this()	
		{
		
			_connectionString = _connectionString.Replace("$PATH$",connectionString);
		
		}
		
		
		public void beginTrans()
		{
			this.Open();
			trans = _connection.BeginTransaction();
		}
		
		public void commit()
		{
			trans.Commit();
			this.Close();
		}
		
		public void rollback()
		{
			trans.Rollback();
			this.Close();
		}
		
		public void Open()	
		{
		
			if (_connection.State != ConnectionState.Open)
		
				_connection.Open();
		
		}
		
		public void Close()	
		{
		
			if (_connection.State != ConnectionState.Closed)
		
				_connection.Close();
		
		}
		
		
		
		public DataSet Query(string sql)	
		{
		
			return Query(sql, null);
		
		}
		
		
		
		public DataSet Query(string sql, params SQLiteParameter[] parameters)	
		{
		
			this.Open();
		
			SQLiteCommand command = ExcDbCommand(sql, parameters);
		
			DataSet ds = new DataSet();
		
			SQLiteDataAdapter da = new SQLiteDataAdapter(command);
		
			da.Fill(ds);
		
			this.Close();
		
			return ds;
		
		}
		
		
		public DataTable QueryDT(string sql)
		{
			DataSet ds = Query(sql);
			
			return ds.Tables[0];
		}
		
		
		public SQLiteDataAdapter QueryDA(string sql, params SQLiteParameter[] parameters)
		{
			this.Open();
		
			SQLiteCommand command = ExcDbCommand(sql, parameters);
		
			
			SQLiteDataAdapter da = new SQLiteDataAdapter(command);
		
			this.Close();
		
			return da;
		}
		
		public SQLiteDataAdapter QueryDA(string sql)
		{		
			return QueryDA(sql, null);
		}
		
		public DataTable QueryDT(string sql, params SQLiteParameter[] parameters)
		{
			DataSet ds = Query(sql, parameters);
			
			return ds.Tables[0];
		}
		
		public int Exc(string sql)	
		{
		
			return Exc(sql, null);
		
		}
		
		
		public SQLiteParameter createSqliteParam(String name, String value)
		{
			return new SQLiteParameter(name,value);
		}
		
		
		public int Exc(string sql, params SQLiteParameter[] parameters)	
		{
		
			SQLiteCommand command = ExcDbCommand(sql, parameters);
		
			this.Open();
		
			int result = command.ExecuteNonQuery();
		
			this.Close();
		
			return result;
		
		}
		
		
		public int ExcWithTrans(string sql, params SQLiteParameter[] parameters)	
		{
		
			SQLiteCommand command = ExcDbCommand(sql, parameters);
		
			int result = command.ExecuteNonQuery();
		
			return result;
		
		}
		

        public Boolean isExist(String sql)
        {
            SQLiteDataReader sdr = Read(sql);

            bool ret = false;

            if (sdr.HasRows)
            {
                ret = true;
            }

            sdr.Dispose();

            return ret;
        }


		public SQLiteDataReader Read(string sql)	
		{
		
			return Read(sql, null);
		
		}
		
		public SQLiteDataReader Read(string sql, params SQLiteParameter[] parameters)	
		{
		
			SQLiteCommand command = ExcDbCommand(sql, parameters);
		
			this.Open();
		
			SQLiteDataReader reader = command.ExecuteReader();
		
			//this.Close();
		
			return reader;
		
		}
		
		SQLiteCommand ExcDbCommand(string sql, SQLiteParameter[] parameters)	
		{
		
			SQLiteCommand command = new SQLiteCommand(sql, _connection);
		
			command.CommandType = CommandType.Text;
		
			if (parameters == null || parameters.Length == 0)
		
				return command;
		
			foreach (SQLiteParameter param in parameters)
		
			{
		
				if(param != null)
		
					command.Parameters.Add(param);
		
			}
		
			return command;
		
		}
	}
}
